package com.dao.impl;

import java.io.Serializable;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dao.TicketDao;
import com.enetiy.Ticket;
import com.enetiy.Ticketuser;
import com.util.PagingUilt;

public class TicketDaoImpl extends HibernateDaoSupport implements TicketDao {

	@Override
	public Serializable insertTickeUser(Ticketuser ticketuser) throws Exception {
		return this.getHibernateTemplate().save(ticketuser);
	}

	@Override
	public void updateTickeUser(Ticketuser ticketuser) throws Exception {
		this.getHibernateTemplate().update(ticketuser);
	}

	@Override
	public Ticketuser selectTicketuser(int id)
			throws Exception {
		return this.getHibernateTemplate().get(Ticketuser.class, id);
	}

	@Override
	public List<Ticket> selectTicketList() throws Exception {
		return this.getHibernateTemplate().find("from Ticket where is_employ = 1");
	}

	@Override
	public List<Ticketuser> selectTicketuserList(int userid) throws Exception {
		Query query = this.getSession().createQuery("from Ticketuser where userid = ?");
		query.setParameter(0, userid);
		List <Ticketuser> list = query.list();
		return list;
	}

	@Override
	public List<Ticketuser> selectTicketuserList(int userid, String iden)
			throws Exception {
		
		String sql = "SELECT tu.* FROM ticketuser AS tu,ticket AS t WHERE tu.`ticketid` = t.`id`" +
				" AND tu.userid=? " +
				" AND t.iden=?";
				
		Query query = this.getSession().createSQLQuery(sql).addEntity(Ticketuser.class);
		query.setParameter(0, userid);
		query.setParameter(1, iden);
		List <Ticketuser> list = query.list();
		return list;
	}

	@Override
	public Ticket selectTicket(String iden) throws Exception {
		Query query = this.getSession().createQuery("from Ticket where iden = ? and is_employ =1");
		query.setParameter(0, iden);
		List <Ticket> list = query.list();
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public void selectTicketuserPU(PagingUilt<Ticketuser> pu,int userid) throws Exception {
		
		int scount = scount(userid);
		pu.setLogCount(scount);
		
		String sql = "select * from ticketuser where is_get = 1 and userid = ? order by id desc";
		Query query = this.getSession().createSQLQuery(sql).addEntity(Ticketuser.class);
		query.setParameter(0, userid);
		int start=(pu.getPageNum()-1)*pu.getPageSize();
		int size = pu.getPageSize();
		query.setFirstResult(start);
		query.setMaxResults(size);
		List <Ticketuser>list = query.list();
		pu.setList(list);
	} 
	
	private int scount(int userid) throws Exception {
		String sql = "select count(id) from ticketuser where is_get = 1 and userid = ?";
		Query query = this.getSession().createSQLQuery(sql);
		query.setParameter(0, userid);
		Integer size = Integer.parseInt(query.uniqueResult().toString());
		return size;
	}
	
	@Override
	public List<Ticketuser> selectTicketuserList(int userid, int isGet)
			throws Exception {
		String sql = "select * from ticketuser where is_get = ? and userid = ?";
		Query query = this.getSession().createSQLQuery(sql).addEntity(Ticketuser.class);
		query.setParameter(0, isGet);
		query.setParameter(1, userid);
		List <Ticketuser> list = query.list();
		return list;
	}
}
